﻿using Koala.Pro.CodeGenerator.UI.Common;
using Koala.Pro.CodeGenerator.UI.DatabaseModel.Schema;
using log4net;
using System.Data;
using System.Data.Entity;
using System.Data.SQLite;
using System.Reflection;

namespace Koala.Pro.CodeGenerator.UI.DatabaseModel.Data
{
    public class Sqlite : Database
    {
        private static readonly ILog log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        public override IDictionary<string, DatabaseInfo> ListDatabases()
        {
            IDictionary<string, DatabaseInfo> dictionary = new Dictionary<string, DatabaseInfo>();
            string databaseName = "";
            string[] parts = base.ConnectString.ToLower().Split(';');
            foreach (string part in parts)
            {
                if (part.StartsWith("Data Source=", StringComparison.OrdinalIgnoreCase))
                {
                    string dataSourcePart = part.Substring("Data Source=".Length).Trim();
                    string fileName = Path.GetFileName(dataSourcePart);
                    databaseName = Path.GetFileNameWithoutExtension(fileName);
                    break;
                }
            }
            if (string.IsNullOrEmpty(databaseName))
            {
                throw new ArgumentNullException("database");
            }

            DatabaseInfo databaseInfo = new DatabaseInfo
            {
                Name = new NameElement(databaseName)
            };

            string key = databaseInfo.Name.Name.ToString();

            if (!dictionary.ContainsKey(key))
            {
                dictionary.Add(key, databaseInfo);
            }

            return dictionary;
        }

        public override IDictionary<string, TableInfo> ListTables(string database)
        {
            if (database == null)
            {
                throw new ArgumentNullException("database");
            }

            IDictionary<string, TableInfo> dictionary = new Dictionary<string, TableInfo>();

            using (SQLiteConnection sqliteConnection = new SQLiteConnection(base.ConnectString))
            {
                sqliteConnection.Open();
                DataTable schema = sqliteConnection.GetSchema("Tables");
                for (int i = 0; i < schema.Rows.Count; i++)
                {
                    string name = schema.Rows[i]["TABLE_NAME"].ToString();
                    TableInfo tableInfo = new TableInfo();
                    tableInfo.Name = new NameElement(name);
                    string key = tableInfo.Name.Name.ToString();
                    if (!dictionary.ContainsKey(key))
                    {
                        dictionary.Add(key, tableInfo);
                    }
                }
            }
            return dictionary;
        }

        public override IDictionary<string, ViewInfo> ListViews(string database)
        {
            return new Dictionary<string, ViewInfo>();
        }

        public override IDictionary<string, ProcedureInfo> ListProcedures(string database)
        {
            return new Dictionary<string, ProcedureInfo>();
        }

        public override bool TestConnection()
        {
            bool result = false;
            IDbConnection dbConnection = null;
            try
            {
                dbConnection = new SQLiteConnection(base.ConnectString);
                if (dbConnection.State == ConnectionState.Closed)
                {
                    dbConnection.Open();
                }
                if (dbConnection.State == ConnectionState.Open)
                {
                    result = true;
                }
            }
            catch
            {
                // Log the exception or handle the error
            }
            finally
            {
                if (dbConnection != null && dbConnection.State != ConnectionState.Closed)
                {
                    dbConnection.Close();
                }
                dbConnection?.Dispose();
            }

            return result;
        }

        public override DataSet ExecuteSql(string database, string query)
        {
            if (string.IsNullOrEmpty(query))
                throw new ArgumentNullException(nameof(query));

            DataSet dataSet = new DataSet();

            using (var connection = new SQLiteConnection(base.ConnectString))
            {
                try
                {
                    connection.Open();

                    using (var dataAdapter = new SQLiteDataAdapter(query, connection))
                    {
                        dataAdapter.Fill(dataSet, Guid.NewGuid().ToString());
                    }
                }
                catch (Exception ex)
                {
                    log.Error("An error occurred while executing the query.", ex);
                    throw;
                }
                finally
                {
                    if (connection.State != ConnectionState.Closed)
                    {
                        connection.Close();
                    }
                }
            }

            return dataSet;
        }

        private DataTable GetTableSchema(string tableName, SQLiteConnection connection)
        {
            DataTable dataTable = new DataTable();
            SQLiteCommand command = new SQLiteCommand
            {
                Connection = connection,
                CommandText = $"SELECT * FROM {tableName} LIMIT 0"
            };

            try
            {
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo))
                {
                    dataTable = reader.GetSchemaTable();
                }
            }
            finally
            {
                command.Dispose();
            }

            return dataTable;
        }

        public override IDictionary<string, ColumnInfo> ListColumns(string database, string tableName)
        {
            if (string.IsNullOrEmpty(database))
            {
                throw new ArgumentNullException(nameof(database));
            }
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException(nameof(tableName));
            }

            IDictionary<string, ColumnInfo> columns = new Dictionary<string, ColumnInfo>();

            using (SQLiteConnection sQLiteConnection = new SQLiteConnection(base.ConnectString))
            {
                sQLiteConnection.Open();

                DataTable dataTable = GetTableSchema(tableName, sQLiteConnection);

                foreach (DataRow row in dataTable.Rows)
                {
                    string name = row["DataType"].ToString();
                    string simplyNetType = GetSimplyNetType(row["DataType"].ToString());
                    var NetType = new NameElement(name, simplyNetType);

                    ColumnInfo columnInfo = new ColumnInfo
                    {
                        Name = new NameElement(row["ColumnName"].ToString()),
                        Ordinal = Convert.ToInt32(row["ColumnOrdinal"]),
                        AllowDBNull = (bool)row["AllowDBNull"],
                        MaxLength = Convert.ToInt32(row["ColumnSize"]),
                        DataTypeId = Convert.ToInt32(row["ProviderType"].ToString()),
                        DataType = row["DataTypeName"].ToString(),
                        AutoIncrement = (bool)row["IsAutoIncrement"],
                        IsPrimaryKey = (bool)row["IsKey"],
                        Unique = (bool)row["IsUnique"],
                        IsReadOnly = (bool)row["IsReadOnly"],
                        NetType = NetType,
                        UIControlType = GetControlType(row["DataTypeName"].ToString())
                        //JavaType = GetJavaType(row["ColumnName"].ToString())
                    };

                    string columnName = columnInfo.Name.Name.ToString();

                    if (!columns.ContainsKey(columnName))
                    {
                        columns.Add(columnName, columnInfo);
                    }
                }

                sQLiteConnection.Close();
            }

            return columns;
        }

        private IList<KeyInfo> GetKeyInfoFromSchema(IDictionary<string, ColumnInfo> columnInfoDict, string tableName, SQLiteConnection connection)
        {
            IList<KeyInfo> keyInfoList = new List<KeyInfo>();
            foreach (KeyValuePair<string, ColumnInfo> column in columnInfoDict)
            {
                DataTable schema = connection.GetSchema("Columns");
                DataRow[] matchingRows = schema.Select(string.Format("TABLE_NAME = '{0}'", tableName), "COLUMN_NAME");
                foreach (DataRow dataRow in matchingRows)
                {
                    string schemaTableName = dataRow["TABLE_NAME"].ToString();
                    string columnName = dataRow["COLUMN_NAME"].ToString();
                    KeyInfo keyInfo = new KeyInfo
                    {
                        TableName = new NameElement(schemaTableName),
                        ColumnName = new NameElement(columnName)
                    };
                    if (base.NoSuffixOfTableName != null)
                    {
                        string aliasTableName = StringUtil.RemovePrefix(schemaTableName, base.NoSuffixOfTableName);
                        log.Debug("Table without suffix: " + aliasTableName);
                        keyInfo.TableName.Alias = new CString(aliasTableName);
                    }
                    keyInfoList.Add(keyInfo);
                }
            }

            return keyInfoList;
        }

        private void SetPrimaryKeyInfo(IDictionary<string, ColumnInfo> columnInfoDict, string tableName, SQLiteConnection connection)
        {
            foreach (KeyValuePair<string, ColumnInfo> column in columnInfoDict)
            {
                DataTable schema = connection.GetSchema("Columns");
                DataRow[] rows = schema.Select("", "COLUMN_NAME");
                foreach (DataRow dataRow in rows)
                {
                    if (column.Value.Name.Name.ToString() == dataRow["COLUMN_NAME"].ToString())
                    {
                        column.Value.IsPrimaryKey = true;
                        column.Value.PKBeReferences = GetKeyInfoFromSchema(columnInfoDict, tableName, connection);
                    }
                }
            }
        }

        private void SetForeignKeyInfo(IDictionary<string, ColumnInfo> columnInfoDict, string tableName, SQLiteConnection connection)
        {
            foreach (KeyValuePair<string, ColumnInfo> column in columnInfoDict)
            {
                DataTable schema = connection.GetSchema("ForeignKeys");

                DataRow[] rows = schema.Select(string.Format("TABLE_NAME = '{0}'", tableName), "COLUMN_NAME");

                foreach (DataRow dataRow in rows)
                {
                    if (column.Value.Name.Name.ToString() == dataRow["COLUMN_NAME"].ToString())
                    {
                        column.Value.IsForeignKey = true;
                        string referencedTable = dataRow["REFERENCED_TABLE_NAME"].ToString();
                        string referencedColumn = dataRow["REFERENCED_COLUMN_NAME"].ToString();

                        KeyInfo keyInfo = new KeyInfo
                        {
                            TableName = new NameElement(referencedTable),
                            ColumnName = new NameElement(referencedColumn)
                        };
                        if (base.NoSuffixOfTableName != null)
                        {
                            string tableNameWithoutSuffix = StringUtil.RemovePrefix(referencedTable, base.NoSuffixOfTableName);
                            log.Debug("Referenced table without suffix: " + tableNameWithoutSuffix);
                            keyInfo.TableName.Alias = new CString(tableNameWithoutSuffix);
                        }

                        column.Value.FKReference = keyInfo;
                    }
                }
            }
        }

        public string GetCreateTableSQL(string tableName)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException(nameof(tableName));
            }
            var query = $"SELECT sql FROM sqlite_master WHERE type = 'table' AND name = '{tableName}'";
            var dataSet = this.ExecuteSql(null, query);
            var table = dataSet.Tables[0];
            var text = "";
            foreach (DataRow row in table.Rows)
            {
                text = row["sql"].ToString();
                break;
            }
            return text;
        }
    }
}